HW#5 --- last modified January 01 1970 00:00:00..

Solution set.

Due date: Dec 6

Files to be submitted:
  problems.zip
  sessions.zip

Purpose: 1) To gain familarity with isolation levels in Oracle as well as granting and revoking privileges. (2) To get used to used to concurrency control and recovery concepts.

Specification:

(1) Do problems 20.21, 20.24, 21.23 out of E&N. Submit these in text or word document that has been zipped in the file problems.zip.

(2) In sessions.zip I want you to submit spools of some sqlplus sessions where you experiment with the ALTER SESSION SET ISOLATION_LEVEL and also with the GRANT command. This file should also contain scripts to create any tables you used during your experiments. In the alter session experiment, I want you to create some table and populate it with a few rows. Then I want you to do two different sqlplus sessions. In the first one set the isolation level to READ COMMITTED and in second one set the isolation level to SERIALIZABLE. In both sessions do a query followed by some kind of update of the rows involved in the query followed by the same query. Compare the results between the two sessions. Do the results match with what you'd expect given the isolation level? For the GRANT usage experiment you need to either work with some partner in the class or have ORACLE at home. Basically, I want you to turn in the spool of a session where you GRANT to PUBLIC the SELECT privileges on your table from the alter session part of the assignment. Then show a spool of your partner actually being able to log in and do a SELECT. The last thing I want you to do is repeat this experiment in another session, but now rather than grant PUBLIC the ability to SELECT grant your partner's login UPDATE privileges.

Point Breakdown